package dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import bean.MonthStatistics;
import bean.MonthStatisticsListBean;
import model.Goods;
import model.Record;
import model.Shop;
import dao.DaoHelper;
import dao.RecordDao;

public class RecordDaoImp implements RecordDao {

	private static RecordDaoImp recordDao = new RecordDaoImp();
	private static DaoHelper daoHelper=DaoHelperImp.getBaseDaoInstance();

	private RecordDaoImp(){}

	public static RecordDaoImp getInstance()
	{
		return recordDao;
	}

	@Override
	public boolean save(Record record) {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		boolean success = true;

		try {	
			stmt=con.prepareStatement("insert into record value(?, ?, ?, ?, ?, ?, ?, ?)");
			stmt.setInt(1, record.getId());
			stmt.setString(2, record.getShop());
			stmt.setString(3, record.getCustomer());
			stmt.setTimestamp(4, record.getTime());
			stmt.setInt(5, record.getGoodsId());
			stmt.setString(6, record.getGoodsName());
			stmt.setInt(7, record.getQuantity());
			stmt.setFloat(8, record.getPrice());
			stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			success = false;
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
		}

		return success;

	}

	@Override
	public List<Record> findAll() {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		ResultSet result=null;
		int i = 0;
		String s = null;
		String c = null;
		Timestamp d = null;
		int g = 0;
		String n = null;
		int q = 0;
		float p = 0;
		Record record = null;
		ArrayList<Record> list = new ArrayList<Record>();

		try {	
			stmt=con.prepareStatement("select * from record");
			result=stmt.executeQuery();
			while (result.next()) {
				i = result.getInt(1);
				s = result.getString(2);
				c = result.getString(3);
				d = result.getTimestamp(4);
				g = result.getInt(5);
				n = result.getString(6);
				q = result.getInt(7);
				p = result.getFloat(8);
				record = new Record(i, s, c, d, g, n, q, p);
				list.add(record);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
			daoHelper.closeResult(result);
		}

		return list;

	}

	@Override
	public Map<Shop, MonthStatisticsListBean> findAllByShopMonth() {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		ResultSet result=null;
		int i = 0;
		String n = null;
		String l = null;
		Shop shop = null;
		ArrayList<Shop> shopList = new ArrayList<Shop>();

		try {	
			stmt=con.prepareStatement("select * from shop");
			result=stmt.executeQuery();
			while (result.next()) {
				i = result.getInt(1);
				n = result.getString(2);
				l = result.getString(3);
				shop = new Shop(i, n, l);
				shopList.add(shop);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
			daoHelper.closeResult(result);
		}

		Connection con2=daoHelper.getConnection();
		PreparedStatement stmt2=null;
		ResultSet result2=null;
		String d = null;
		float p = 0;
		MonthStatistics statistics = null;
		ArrayList<MonthStatistics> list = null;
		HashMap<Shop, MonthStatisticsListBean> map = new HashMap<Shop, MonthStatisticsListBean>();
		try {
			for (int j = 0; j < shopList.size(); j++) {
				list = new ArrayList<MonthStatistics>();
				stmt2=con2.prepareStatement("select DATE_FORMAT(time, '%Y-%m') as month, sum(price) from record where shop = '" + shopList.get(j).getName() + "' group by month");
				result2=stmt2.executeQuery();
				while (result2.next()) {
					d = result2.getString(1);
					p = result2.getFloat(2);
					statistics = new MonthStatistics(d, p);
					list.add(statistics);
				}
				map.put(shopList.get(j), new MonthStatisticsListBean(list));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			daoHelper.closeConnection(con2);
			daoHelper.closePreparedStatement(stmt2);
			daoHelper.closeResult(result2);
		}

		return map;
	}

	@Override
	public List<MonthStatistics> findAllByMonth() {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		ResultSet result=null;
		String d = null;
		float p = 0;
		MonthStatistics statistics = null;
		ArrayList<MonthStatistics> list = new ArrayList<MonthStatistics>();

		try {	
			stmt=con.prepareStatement("select DATE_FORMAT(time, '%Y-%m') as month, sum(price) from record group by month");
			result=stmt.executeQuery();
			while (result.next()) {
				d = result.getString(1);
				p = result.getFloat(2);
				statistics = new MonthStatistics(d, p);
				list.add(statistics);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
			daoHelper.closeResult(result);
		}

		return list;
	}

	@Override
	public List<Goods> findHotSixGoods() {
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		ResultSet result=null;
		int id = 0;
		String name = null;
		int index = 0;
		ArrayList<Goods> list = new ArrayList<Goods>();

		try {	
			stmt=con.prepareStatement("select goodsId, goodsName from record group by goodsId, goodsName order by sum(quantity) desc");
			result=stmt.executeQuery();
			while (result.next() && index < 6) {
				id = result.getInt(1);
				name = result.getString(2);
				Goods goods = new Goods(id, name);
				list.add(goods);
				index++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
			daoHelper.closeResult(result);
		}

		return list;
	}

	@Override
	public List<Record> find(String customer) {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		ResultSet result=null;
		int i = 0;
		String s = null;
		String c = null;
		Timestamp d = null;
		int g = 0;
		String n = null;
		int q = 0;
		float p = 0;
		Record record = null;
		ArrayList<Record> list = new ArrayList<Record>();

		try {	
			stmt=con.prepareStatement("select * from record where customer = ?");
			stmt.setString(1, customer);
			result=stmt.executeQuery();
			while (result.next()) {
				i = result.getInt(1);
				s = result.getString(2);
				c = result.getString(3);
				d = result.getTimestamp(4);
				g = result.getInt(5);
				n = result.getString(6);
				q = result.getInt(7);
				p = result.getFloat(8);
				record = new Record(i, s, c, d, g, n, q, p);
				list.add(record);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
			daoHelper.closeResult(result);
		}

		return list;

	}

	@Override
	public boolean removeByCustomer(String customer) {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		boolean success = true;

		try {	
			stmt=con.prepareStatement("delete from record where customer = ?");
			stmt.setString(1, customer);
			stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			success = false;
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
		}

		return success;
	}

	@Override
	public Map<Shop, Float> sumByShop() {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		ResultSet result=null;
		int i = 0;
		String n = null;
		String l = null;
		Shop shop = null;
		ArrayList<Shop> shopList = new ArrayList<Shop>();

		try {	
			stmt=con.prepareStatement("select * from shop");
			result=stmt.executeQuery();
			while (result.next()) {
				i = result.getInt(1);
				n = result.getString(2);
				l = result.getString(3);
				shop = new Shop(i, n, l);
				shopList.add(shop);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
			daoHelper.closeResult(result);
		}

		Connection con2=daoHelper.getConnection();
		PreparedStatement stmt2=null;
		ResultSet result2=null;
		float p = 0;
		HashMap<Shop, Float> map = new HashMap<Shop, Float>();
		try {
			for (int j = 0; j < shopList.size(); j++) {
				stmt2=con2.prepareStatement("select sum(price) from record where shop = '" + shopList.get(j).getName() + "'");
				result2=stmt2.executeQuery();
				while (result2.next()) {
					p = result2.getFloat(1);
				}
				map.put(shopList.get(j), p);
			}
			stmt2=con2.prepareStatement("select sum(price) from record where shop is NULL");
			result2=stmt2.executeQuery();
			while (result2.next()) {
				p = result2.getFloat(1);
			}
			map.put(null, p);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			daoHelper.closeConnection(con2);
			daoHelper.closePreparedStatement(stmt2);
			daoHelper.closeResult(result2);
		}

		return map;
	}

}
